package web.play.rss.util.stock.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import web.play.rss.util.dao.DBConnSourceByStruts;
import web.play.rss.util.stock.bean.ConverterBean;
import web.play.rss.util.stock.bean.StockBean;


import com.c.elmer.util.string.MyString;

public class StockDAO {

	public int getActiveStockCount (){
		int count = 0;

		String sql = "select count(Oid) from Stock where StockActive >= 2";
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = new DBConnSourceByStruts().getConnection();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		try{
			ps = con.prepareStatement(sql);
			
			rs = ps.executeQuery();
			
			while(rs.next()){
				count = rs.getInt(1);
				break;
			}
			
			
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			try{
				ps.close();
				
				con.close();				
			}catch (Exception e) {
				e.printStackTrace();
			}
			
		}
		return count;
	}

	public int getCheckedStockCount(){
		int count = 0;
		
		String sql = "select count(Oid) from Stock where StockActive = 3";
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = new DBConnSourceByStruts().getConnection();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		try{
			ps = con.prepareStatement(sql);
			
			rs = ps.executeQuery();
			
			while(rs.next()){
				count = rs.getInt(1);
				break;
			}
			
			
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			try{
				ps.close();
				
				con.close();				
			}catch (Exception e) {
				e.printStackTrace();
			}
			
		}
		return count;
	}

	public int getNoCheckedStockCount(){
		int count = 0;
		
		String sql = "select count(Oid) from Stock where StockActive < 3";
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = new DBConnSourceByStruts().getConnection();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		try{
			ps = con.prepareStatement(sql);
			
			rs = ps.executeQuery();
			
			while(rs.next()){
				count = rs.getInt(1);
				break;
			}
			
			
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			try{
				ps.close();
				
				con.close();				
			}catch (Exception e) {
				e.printStackTrace();
			}
			
		}
		return count;
	}
	
	public List listStockPage(int page,int row){
		List list = new ArrayList();

		String sql = "select * from Stock where StockActive >= 2 order by Oid limit ?,?";
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = new DBConnSourceByStruts().getConnection();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		try{
			ps = con.prepareStatement(sql);
			ps.setInt(1, (page - 1 ) * row);
			ps.setInt(2, row);
			
			rs = ps.executeQuery();
			
			while(rs.next()){
				StockBean bean = new StockBean();
				
				bean.setOid(rs.getString(1));
				bean.setStockNum(rs.getString(2));
				bean.setStockName(rs.getString(3));
				bean.setStockType(rs.getString(4));
				bean.setStockActive(rs.getString(5));
				
				list.add(bean);
			}
			
			
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			try{
				ps.close();
				
				con.close();				
			}catch (Exception e) {
				e.printStackTrace();
			}
			
		}
		
		
		return list;
	}

	public boolean updateStock(String stocknum,String stockActive,String stockname,String stocktype,String stockaddr){
		boolean isupdate = false;
		String sql = "update Stock set StockActive = ?,StockName =?,StockType=?,StockAddr=? where StockNum = ? and StockActive = 2";
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = new DBConnSourceByStruts().getConnection();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		
		try{

			ps = con.prepareStatement(sql);
			ps.setString(1, stockActive);
			ps.setString(2, stockname);
			ps.setString(3, stocktype);
			ps.setString(4, stockaddr);
			ps.setString(5, stocknum);
			
			isupdate = ps.execute();
			
			
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			try{
				ps.close();
				
				con.close();				
			}catch (Exception e) {
				e.printStackTrace();
			}
			
		}
		
		return isupdate;
	}

	
	public boolean updateStockActive(String stocknum){
		boolean isupdate = false;

		String sql = "update Stock set StockActive = 1 where StockNum = ?";
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = new DBConnSourceByStruts().getConnection();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		
		try{

			ps = con.prepareStatement(sql);
			ps.setString(1, stocknum);
			
			isupdate = ps.execute();
			
			
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			try{
				ps.close();
				
				con.close();				
			}catch (Exception e) {
				e.printStackTrace();
			}
			
		}
		
		return isupdate;
	}

	
	public boolean deleteStockByActiveNum(String activenum){
		boolean isupdate = false;
		
		String sql = "delete from Stock where StockActive = ?";
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = new DBConnSourceByStruts().getConnection();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		
		try{
			
			ps = con.prepareStatement(sql);
			ps.setString(1, activenum);
			
			isupdate = ps.execute();
			
			
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			try{
				ps.close();
				
				con.close();				
			}catch (Exception e) {
				e.printStackTrace();
			}
			
		}
		
		return isupdate;
	}
	
	
	public int getConverCount(){
		int count = 0;
		String sql = "select count(Oid) from Converter";
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = new DBConnSourceByStruts().getConnection();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		
		try{
			
			ps = con.prepareStatement(sql);
			
			ResultSet rs = ps.executeQuery();
			
			while(rs.next()){
				
				count = rs.getInt(1);
				break;
			}
			
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			try{
				ps.close();
				
				con.close();				
			}catch (Exception e) {
				e.printStackTrace();
			}
			
		}
		
		return count;
	}
	
	
	public List getConterver(int page,int row){
		List list = new ArrayList();
		String sql = "select * from Converter limit ?,?";
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = new DBConnSourceByStruts().getConnection();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		
		try{
			
			ps = con.prepareStatement(sql);
			ps.setInt(1, (page - 1 ) * row);
			ps.setInt(2, row);
			
			ResultSet rs = ps.executeQuery();
			
			while(rs.next()){
				
				ConverterBean bean = new ConverterBean();
				bean.setOid(rs.getInt(1));
				bean.setEn_name(MyString.isotoGbk(rs.getString(2)));
				bean.setCn_name(MyString.isotoGbk(rs.getString(3)));
				bean.setType(rs.getString(4));
				bean.setPrice(rs.getString(5));
				
				list.add(bean);
			}
			
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			try{
				ps.close();
				
				con.close();				
			}catch (Exception e) {
				e.printStackTrace();
			}
			
		}
		
		return list;
	}
	
	public boolean addConterver(String us_name,String cn_name,String type,String price){
		String sql = "replace into Converter ( ConverterNum, ConverterName, ConverterType, ConverterPrice) values (?, ?, ?, ?)";
		boolean isadd = false;
		
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = new DBConnSourceByStruts().getConnection();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		try{

			ps = con.prepareStatement(sql);
			ps.setString(1, MyString.isotoGbk(us_name));
			ps.setString(2, new String(cn_name.getBytes("gbk"),"iso-8859-1"));
			ps.setString(3, MyString.isotoGbk(type));
			ps.setString(4, MyString.gbktoIso(price));
			
			isadd = ps.execute();
			
			
		}catch (Exception e) {
			System.err.println("getConverter error!");
			//e.printStackTrace();
		}finally{
			try{
				ps.close();
				
				con.close();				
			}catch (Exception e) {
				e.printStackTrace();
			}
			
		}
		return isadd ;
	}

}
